﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace StudentManage
{
    public partial class FrmStudentList : Form
    {
        public FrmStudentList()
        {
            InitializeComponent();
        }
        //内置委托两种： Action<int>   Func<int>  Func<string,string>
        //相同点：可以带参也可不带参，带参最多带16个参数
        //不同点：Action 不带返回值， Func 带一个返回值
        private Action reLoad = null;
        //单例  只有一个实例
        private static FrmStudentList frmStudentList = null;
        public static FrmStudentList CreateInstance()
        {
            if (frmStudentList == null || frmStudentList.IsDisposed)
            {
                return frmStudentList = new FrmStudentList();
            }
            return frmStudentList;
        }
        /// <summary>
        /// 加载页面
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void FrmStudentList_Load(object sender, EventArgs e)
        {
            InitClassInfo();//加载班级信息
            InitAllStudent();//加载所有学生信息
        }

        private void InitAllStudent()
        {
            string sql = "SELECT StuId,StuName,ClassName,GradeName,Sex,Phone " +
                         "FROM studentinfo s JOIN classinfo c ON s.`ClassId`= c.`ClassId` " +
                         "JOIN gradeinfo g ON c.`GradeId`= g.`GradeId` where IsDeleted=0";
            DataTable dtStuList = SqlHelper.GetDataTable(sql);
            if (dtStuList.Rows.Count > 0)
            {
                foreach (DataRow row in dtStuList.Rows)
                {
                    string className = row["ClassName"].ToString();
                    string gradeName = row["GradeName"].ToString();
                    row["ClassName"] = className + "-" + gradeName;
                }
            }
            //只显示指定的列
            //方式一：设置属性
            //dgvStuList.AutoGenerateColumns = false;
            //方式二：移除指定列
            dtStuList.Columns.Remove(dtStuList.Columns[3]);
            dgvStuList.DataSource = dtStuList;
        }

        private void InitClassInfo()
        {
            string sqlClass = "SELECT c.ClassId,g.GradeName,c.ClassName " +
                              "FROM gradeinfo g,classinfo c WHERE g.GradeId = c.GradeId";
            DataTable dtClassList = SqlHelper.GetDataTable(sqlClass);
            if (dtClassList.Rows.Count > 0)
            {
                foreach (DataRow row in dtClassList.Rows)
                {
                    string className = row["ClassName"].ToString();
                    string gradeName = row["GradeName"].ToString();
                    row["ClassName"] = className + "--" + gradeName;
                }            
            }
            //添加一个新行“请选择”
             DataRow dr =  dtClassList.NewRow();
            dr["ClassId"] = 0;
            dr["ClassName"] = "请选择";
            //把新行添加到datattable的第一行
            dtClassList.Rows.InsertAt(dr,0);
            //设置数据源
            cboClasses.DataSource = dtClassList;
            cboClasses.DisplayMember = "ClassName";
            cboClasses.ValueMember = "ClassId";
            //默认选中第一行
            cboClasses.SelectedIndex = 0;
        }
        /// <summary>
        /// 条件查询按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnFind_Click(object sender, EventArgs e)
        {
            //获取页面输入信息
            int classId = (int)cboClasses.SelectedValue;
            string stuName = txtStuName.Text.Trim();
            //SQL语句
            string sql = "SELECT StuId,StuName,ClassName,GradeName,Sex,Phone " +
                        "FROM studentinfo s JOIN classinfo c ON s.`ClassId`= c.`ClassId` " +
                        "JOIN gradeinfo g ON c.`GradeId`= g.`GradeId`";
            sql += " where 1=1 and s.IsDeleted=0";
            //判断是否为空
            if (classId > 0)
            {
                sql += " and s.ClassId=@ClassId "; 
            }
            if (!string.IsNullOrEmpty(stuName))
            {
                sql += "and s.StuName like @StuName";
            }

            sql += "order by s.StuId";
            //设置查询参数
            MySqlParameter[] paras =
            {
            new MySqlParameter("@ClassId",classId),
            new MySqlParameter("@StuName","%"+stuName+"%")
            };
            //执行sql
            DataTable dtStuList = SqlHelper.GetDataTable(sql,paras);
            if (dtStuList.Rows.Count > 0)
            {
                foreach (DataRow row in dtStuList.Rows)
                {
                    string className = row["ClassName"].ToString();
                    string gradeName = row["GradeName"].ToString();
                    row["ClassName"] = className + "-" + gradeName;
                }
            }
            //绑定数据源
            dgvStuList.DataSource = dtStuList;
        }

        /// <summary>
        /// 修改或删除功能实现
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void dgvStuList_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            //保证不是标题行 
            if (e.RowIndex != -1)
            {
                //获取要操作的该行数据
                DataRow dr = (dgvStuList.Rows[e.RowIndex].DataBoundItem as DataRowView).Row;
                //获取到点击的单元格
                DataGridViewCell cell = dgvStuList.Rows[e.RowIndex].Cells[e.ColumnIndex];
                //判断点击的是修改列还是删除列
                if (cell is DataGridViewLinkCell && cell.FormattedValue.ToString() == "修改")
                {
                    //修改操作，打开修改页面，把StuId传过去 
                    //传值：1，构造函数传值，2，Tag 传值 ，3，公共变量传值 （注意：1，3慎用，推荐Tag）
                    int stuId = (int)dr["StuId"];//获取id
                   //FrmEditStudent frmEdit = new FrmEditStudent(stuId);//1,构造函数传值
                    FrmEditStudent frmEdit = new FrmEditStudent();
                    reLoad = InitAllStudent;//把本页面的InitAllStudent方法委托给reLoad
                   // frmEdit.Tag = stuId; //2，Tag 传值
                   //tag对象传值
                    frmEdit.Tag = new TagObject()
                    {
                        StuId = stuId,
                        ReLoad = reLoad
                    };
                    //frmEdit.pubStuId = stuId; //3，公共变量传值
                    frmEdit.MdiParent = this.MdiParent;//指定父窗体
                    frmEdit.Show();

                }
                else if (cell is DataGridViewLinkCell && cell.FormattedValue.ToString() == "删除")
                {
                    //删除操作
                    if (MessageBox.Show("您确定删除该条信息吗？", "删除信息提示",
                        MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                    {                        
                        //获取改行的id
                        int stuId = int.Parse(dr["StuId"].ToString());
                        // 1, 假删除 IsDeleted 0-->1 更新操作
                        string sqlDelUpdate = "update studentInfo set IsDeleted=1 where StuId=@StuId";
                        MySqlParameter para = new MySqlParameter("@StuId", stuId);
                        int rs = SqlHelper.ExecuteNonQuery(sqlDelUpdate, para);
                        if (rs > 0)
                        {
                            MessageBox.Show("删除成功", "删除信息提示",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
                            //此时DataGridView内数据没有刷新，需手动刷新
                            DataTable dt = (DataTable)dgvStuList.DataSource;
                            //移除前面获取的该行数据即可
                            dt.Rows.Remove(dr);
                            //重新绑定数据源
                            dgvStuList.DataSource = dt;
                        }
                        else
                        {
                            MessageBox.Show("删除失败", "删除信息提示",
                               MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }
                        #region  真删除操作
                        // 2, 真删除（慎用）
                        //string sqlDel = "delete from  studentInfo where StuId=@StuId";
                        //MySqlParameter paraDel = new MySqlParameter("@StuId", stuId);
                        //int rs1 = SqlHelper.ExecuteNonQuery(sqlDel, paraDel);
                        //if (rs1 > 0)
                        //{
                        //    MessageBox.Show("删除成功", "删除信息提示",
                        //    MessageBoxButtons.OK, MessageBoxIcon.Information);
                        //    //此时DataGridView内数据没有刷新，需手动刷新
                        //    DataTable dt = (DataTable)dgvStuList.DataSource;
                        //    //移除前面获取的该行数据即可
                        //    dt.Rows.Remove(drDel);
                        //    //重新绑定数据源
                        //    dgvStuList.DataSource = dt;
                        //}
                        //else
                        //{
                        //    MessageBox.Show("删除失败", "删除信息提示",
                        //       MessageBoxButtons.OK, MessageBoxIcon.Error);
                        //}
                        #endregion
                    }
                }
            }            
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnDelete_Click(object sender, EventArgs e)
        {
            //选择要删除的数据项
            //获取要删除的数据 StuId
            //判断 选择的个数 ==0，提示用户选择，>0时 继续
            //删除操作  事务  SQL事务， 代码里启动事务
            List<int> listIds = new List<int>();
            //遍历checkBox
            for (int i = 0; i < dgvStuList.Rows.Count; i++)
            { 
                //取到每个CheckBox
                DataGridViewCheckBoxCell cell = dgvStuList.Rows[i].Cells["colCheckBox"] as DataGridViewCheckBoxCell;
                //判断是否被选中
                bool chk = Convert.ToBoolean(cell.Value);
                if (chk) //选中
                {
                    //获取行
                    DataRow dr = (dgvStuList.Rows[i].DataBoundItem as DataRowView).Row;
                    //获取id
                    int stuId = (int)dr["StuId"];
                    listIds.Add(stuId);
                }
            }
            //真删除
            if (listIds.Count == 0)
            {
                MessageBox.Show("请选择要删除的数据", "删除信息提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            else
            {
                int count = 0;
                if (MessageBox.Show("您确定删除吗？", "删除信息提示",
                           MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    using (MySqlConnection conn = new MySqlConnection(SqlHelper.connStr))
                    {
                        //事务是通过conn开启的，conn.open
                        conn.Open();
                        MySqlTransaction trans = conn.BeginTransaction();
                        //sqlCommand 事务执行 cmd
                        MySqlCommand cmd = new MySqlCommand();
                        cmd.Connection = conn;
                        cmd.Transaction = trans;
                        try
                        {
                            foreach (int id in listIds)
                            {
                                cmd.CommandText = "delete from studentInfo where StuId=@StuId";
                                MySqlParameter para = new MySqlParameter("@StuId", id);
                                cmd.Parameters.Clear();
                                cmd.Parameters.Add(para);
                                count += cmd.ExecuteNonQuery();
                            }
                            trans.Commit();
                        }
                        catch (SqlException ex)
                        {
                            trans.Rollback();
                            MessageBox.Show("删除失败", "删除信息提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }
                    }
                    if (count == listIds.Count)
                    {
                        MessageBox.Show("删除成功", "删除信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        //手动刷新
                        DataTable dtStudents = dgvStuList.DataSource as DataTable;
                        string strIds = string.Join(",",listIds);
                        DataRow[] rows = dtStudents.Select("StuId in ("+strIds+")");
                        foreach (DataRow row in rows)
                        {
                            dtStudents.Rows.Remove(row);
                        }
                        dgvStuList.DataSource = dtStudents;
                    }
                }
            }

        }
    }
}
